How to use explain plan

This is one of the most powerful tools you have for understanding why a query is slow or not returning the results you think it should. It’s quite simple to use:

explain select * from customers where customer_name = 'Bob';
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customers  (cost=0.00..25.88 rows=6 width=36)
   Filter: (customer_name = 'Bob'::text)
(2 rows)

It will do a sequential scan on the table and create a result set with all the rows that match the filter. Easy enough.

banana=# create index try_me on customers(customer_name);
CREATE INDEX
banana=# explain select * from customers where customer_name = 'Bob';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on customers  (cost=0.00..1.05 rows=1 width=36)
   Filter: (customer_name = 'Bob'::text)
(2 rows)

Note that Postgres knows the table is so small an index scan is unnecessary. Let’s add a few more rows.

banana=# INSERT INTO customers (customer_name)
SELECT
  CASE
    WHEN n <= 20 THEN 'Bob'
    ELSE 'Customer ' || n
  END
FROM generate_series(1, 1000) AS n;
banana=# vacuum;
banana=# explain select * from customers where customer_name = 'Bob';                                                                                                                          QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on customers  (cost=4.43..11.68 rows=20 width=16)
   Recheck Cond: (customer_name = 'Bob'::text)
   ->  Bitmap Index Scan on try_me  (cost=0.00..4.43 rows=20 width=0)
         Index Cond: (customer_name = 'Bob'::text)
(4 rows)

Note we had do use the vacuum Postgres command to make it re-analyse the database so it knows there are enough rows in the customers table to justify using the index. I told this script to create some rows with the customer name of Bob but in fact it doesn’t matter for explain because the query is not run.

You can see that it will now use the index to home in on the result set it needs. For more details of explain read the Postgres documentation.